{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "## 2.6 规整数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 1.轴向连接"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.148361Z",
     "end_time": "2024-05-09T13:59:07.763681Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "a    100\nb    200\nc    300\nd    400\ne    500\nf    600\ndtype: int64"
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "s1 = pd.Series([100, 200, 300], index=['a', 'b', 'c'])\n",
    "s2 = pd.Series([400, 500, 600], index=['d', 'e', 'f'])\n",
    "pd.concat([s1, s2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "     0    1    2\n0  110  120  130\n1  210  220  230\n2  310  320  330\n0   11   12   13\n1   21   22   23\n2   31   32   33",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>110</td>\n      <td>120</td>\n      <td>130</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>210</td>\n      <td>220</td>\n      <td>230</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>310</td>\n      <td>320</td>\n      <td>330</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>11</td>\n      <td>12</td>\n      <td>13</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>21</td>\n      <td>22</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>31</td>\n      <td>32</td>\n      <td>33</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame([[110, 120, 130], [210, 220, 230], [310, 320, 330]])\n",
    "df2 = pd.DataFrame([[11, 12, 13], [21, 22, 23], [31, 32, 33]])\n",
    "pd.concat([df1, df2])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.763681Z",
     "end_time": "2024-05-09T13:59:07.776192Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "     0      1      2\n0  110  120.0  130.0\n1  210  220.0  230.0\n2  310  320.0  330.0\na  100    NaN    NaN\nb  200    NaN    NaN\nc  300    NaN    NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>110</td>\n      <td>120.0</td>\n      <td>130.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>210</td>\n      <td>220.0</td>\n      <td>230.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>310</td>\n      <td>320.0</td>\n      <td>330.0</td>\n    </tr>\n    <tr>\n      <th>a</th>\n      <td>100</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>200</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>300</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, s1])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.776192Z",
     "end_time": "2024-05-09T13:59:07.808386Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "         0      1      2\ndf1 0  110  120.0  130.0\n    1  210  220.0  230.0\n    2  310  320.0  330.0\ns1  a  100    NaN    NaN\n    b  200    NaN    NaN\n    c  300    NaN    NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">df1</th>\n      <th>0</th>\n      <td>110</td>\n      <td>120.0</td>\n      <td>130.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>210</td>\n      <td>220.0</td>\n      <td>230.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>310</td>\n      <td>320.0</td>\n      <td>330.0</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">s1</th>\n      <th>a</th>\n      <td>100</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>200</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>300</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, s1], keys=['df1', 's1'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.787305Z",
     "end_time": "2024-05-09T13:59:07.862182Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "     0    1    2   0   1   2\n0  110  120  130  11  12  13\n1  210  220  230  21  22  23\n2  310  320  330  31  32  33",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>110</td>\n      <td>120</td>\n      <td>130</td>\n      <td>11</td>\n      <td>12</td>\n      <td>13</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>210</td>\n      <td>220</td>\n      <td>230</td>\n      <td>21</td>\n      <td>22</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>310</td>\n      <td>320</td>\n      <td>330</td>\n      <td>31</td>\n      <td>32</td>\n      <td>33</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.806335Z",
     "end_time": "2024-05-09T13:59:07.862717Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "     0    1    2\na  110  120  130\nc  210  220  230\nd  310  320  330",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>110</td>\n      <td>120</td>\n      <td>130</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>210</td>\n      <td>220</td>\n      <td>230</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310</td>\n      <td>320</td>\n      <td>330</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.index = ['a', 'c', 'd']\n",
    "df1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.811077Z",
     "end_time": "2024-05-09T13:59:07.879199Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "    0   1   2\nb  11  12  13\nc  21  22  23\nd  31  32  33",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>b</th>\n      <td>11</td>\n      <td>12</td>\n      <td>13</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>21</td>\n      <td>22</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>31</td>\n      <td>32</td>\n      <td>33</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.index = ['b', 'c', 'd']\n",
    "df2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.819626Z",
     "end_time": "2024-05-09T13:59:07.888198Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "       0      1      2     0     1     2\na  110.0  120.0  130.0   NaN   NaN   NaN\nc  210.0  220.0  230.0  21.0  22.0  23.0\nd  310.0  320.0  330.0  31.0  32.0  33.0\nb    NaN    NaN    NaN  11.0  12.0  13.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>110.0</td>\n      <td>120.0</td>\n      <td>130.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>210.0</td>\n      <td>220.0</td>\n      <td>230.0</td>\n      <td>21.0</td>\n      <td>22.0</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310.0</td>\n      <td>320.0</td>\n      <td>330.0</td>\n      <td>31.0</td>\n      <td>32.0</td>\n      <td>33.0</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>11.0</td>\n      <td>12.0</td>\n      <td>13.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.830513Z",
     "end_time": "2024-05-09T13:59:07.931573Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "     0    1    2   0   1   2\nc  210  220  230  21  22  23\nd  310  320  330  31  32  33",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n      <th>0</th>\n      <th>1</th>\n      <th>2</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>c</th>\n      <td>210</td>\n      <td>220</td>\n      <td>230</td>\n      <td>21</td>\n      <td>22</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310</td>\n      <td>320</td>\n      <td>330</td>\n      <td>31</td>\n      <td>32</td>\n      <td>33</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], axis=1, join='inner')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.843185Z",
     "end_time": "2024-05-09T13:59:07.931573Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "LEVEL level1               level2            \nROW      one    two  three    one three  five\na      110.0  120.0  130.0    NaN   NaN   NaN\nc      210.0  220.0  230.0   21.0  22.0  23.0\nd      310.0  320.0  330.0   31.0  32.0  33.0\nb        NaN    NaN    NaN   11.0  12.0  13.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th>LEVEL</th>\n      <th colspan=\"3\" halign=\"left\">level1</th>\n      <th colspan=\"3\" halign=\"left\">level2</th>\n    </tr>\n    <tr>\n      <th>ROW</th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>one</th>\n      <th>three</th>\n      <th>five</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>110.0</td>\n      <td>120.0</td>\n      <td>130.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>210.0</td>\n      <td>220.0</td>\n      <td>230.0</td>\n      <td>21.0</td>\n      <td>22.0</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310.0</td>\n      <td>320.0</td>\n      <td>330.0</td>\n      <td>31.0</td>\n      <td>32.0</td>\n      <td>33.0</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>11.0</td>\n      <td>12.0</td>\n      <td>13.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.columns = ['one', 'two', 'three']\n",
    "df2.columns = ['one', 'three', 'five']\n",
    "pd.concat([df1, df2], axis=1, keys=['level1', 'level2'], names=['LEVEL', 'ROW'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.858789Z",
     "end_time": "2024-05-09T13:59:07.931573Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "LEVEL level1               level2            \nROW      one    two  three    one three  five\na      110.0  120.0  130.0    NaN   NaN   NaN\nc      210.0  220.0  230.0   21.0  22.0  23.0\nd      310.0  320.0  330.0   31.0  32.0  33.0\nb        NaN    NaN    NaN   11.0  12.0  13.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead tr th {\n        text-align: left;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr>\n      <th>LEVEL</th>\n      <th colspan=\"3\" halign=\"left\">level1</th>\n      <th colspan=\"3\" halign=\"left\">level2</th>\n    </tr>\n    <tr>\n      <th>ROW</th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>one</th>\n      <th>three</th>\n      <th>five</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>110.0</td>\n      <td>120.0</td>\n      <td>130.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>210.0</td>\n      <td>220.0</td>\n      <td>230.0</td>\n      <td>21.0</td>\n      <td>22.0</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310.0</td>\n      <td>320.0</td>\n      <td>330.0</td>\n      <td>31.0</td>\n      <td>32.0</td>\n      <td>33.0</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>11.0</td>\n      <td>12.0</td>\n      <td>13.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat({\"level1\": df1, \"level2\": df2}, axis=1, names=['LEVEL', 'ROW'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.876197Z",
     "end_time": "2024-05-09T13:59:07.983303Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "   one    two  three  five\na  110  120.0    130   NaN\nc  210  220.0    230   NaN\nd  310  320.0    330   NaN\nb   11    NaN     12  13.0\nc   21    NaN     22  23.0\nd   31    NaN     32  33.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>five</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>110</td>\n      <td>120.0</td>\n      <td>130</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>210</td>\n      <td>220.0</td>\n      <td>230</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>310</td>\n      <td>320.0</td>\n      <td>330</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>11</td>\n      <td>NaN</td>\n      <td>12</td>\n      <td>13.0</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>21</td>\n      <td>NaN</td>\n      <td>22</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>31</td>\n      <td>NaN</td>\n      <td>32</td>\n      <td>33.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1._append(df2)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.900581Z",
     "end_time": "2024-05-09T13:59:08.053881Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "   one    two  three  five\n0  110  120.0    130   NaN\n1  210  220.0    230   NaN\n2  310  320.0    330   NaN\n3   11    NaN     12  13.0\n4   21    NaN     22  23.0\n5   31    NaN     32  33.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n      <th>five</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>110</td>\n      <td>120.0</td>\n      <td>130</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>210</td>\n      <td>220.0</td>\n      <td>230</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>310</td>\n      <td>320.0</td>\n      <td>330</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>11</td>\n      <td>NaN</td>\n      <td>12</td>\n      <td>13.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>21</td>\n      <td>NaN</td>\n      <td>22</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>31</td>\n      <td>NaN</td>\n      <td>32</td>\n      <td>33.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1._append(df2, ignore_index=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.914172Z",
     "end_time": "2024-05-09T13:59:08.053881Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 2.合并数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "        city   number\n0   shanghai  27466.2\n1  guangzhou  19610.9\n2   shenzhen  19492.6\n3  chognqing  17558.8",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>number</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>shanghai</td>\n      <td>27466.2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>guangzhou</td>\n      <td>19610.9</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>shenzhen</td>\n      <td>19492.6</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>chognqing</td>\n      <td>17558.8</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp1 = pd.DataFrame({\"city\": [\"shanghai\", \"guangzhou\", \"shenzhen\", \"chognqing\"],\n",
    "                     \"number\": [27466.2, 19610.9, 19492.6, 17558.8]})\n",
    "gdp1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.924876Z",
     "end_time": "2024-05-09T13:59:08.053881Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "       city   number\n0  shanghai  27466.2\n1   beijing  24899.3\n2  shenzhen  19492.6\n3    suzhou  15475.1",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>number</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>shanghai</td>\n      <td>27466.2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>beijing</td>\n      <td>24899.3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>shenzhen</td>\n      <td>19492.6</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>suzhou</td>\n      <td>15475.1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdp2 = pd.DataFrame({\"city\": [\"shanghai\", \"beijing\", \"shenzhen\", \"suzhou\"],\n",
    "                     \"number\": [27466.2, 24899.3, 19492.6, 15475.1]})\n",
    "gdp2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.933576Z",
     "end_time": "2024-05-09T13:59:08.091855Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "       city   number\n0  shanghai  27466.2\n1  shenzhen  19492.6",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>number</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>shanghai</td>\n      <td>27466.2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>shenzhen</td>\n      <td>19492.6</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(gdp1, gdp2)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.940674Z",
     "end_time": "2024-05-09T13:59:08.101240Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "        city   number      city   number\n0   shanghai  27466.2  shanghai  27466.2\n1  guangzhou  19610.9   beijing  24899.3\n2   shenzhen  19492.6  shenzhen  19492.6\n3  chognqing  17558.8    suzhou  15475.1",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>number</th>\n      <th>city</th>\n      <th>number</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>shanghai</td>\n      <td>27466.2</td>\n      <td>shanghai</td>\n      <td>27466.2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>guangzhou</td>\n      <td>19610.9</td>\n      <td>beijing</td>\n      <td>24899.3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>shenzhen</td>\n      <td>19492.6</td>\n      <td>shenzhen</td>\n      <td>19492.6</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>chognqing</td>\n      <td>17558.8</td>\n      <td>suzhou</td>\n      <td>15475.1</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([gdp1, gdp2], join='inner', axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.947711Z",
     "end_time": "2024-05-09T13:59:08.140955Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "  grade  ldata\n0     a      0\n1     b      1\n2     b      2\n3     a      3\n4     c      4\n5     c      5\n6     d      6",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>grade</th>\n      <th>ldata</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>b</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>a</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>c</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>c</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>d</td>\n      <td>6</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_df = pd.DataFrame({\"grade\": ['a', 'b', 'b', 'a', 'c', 'c', 'd'], \"ldata\": range(7)})\n",
    "left_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.957232Z",
     "end_time": "2024-05-09T13:59:08.214919Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [
    {
     "data": {
      "text/plain": "  grade  rdata\n0     a     30\n1     b     40\n2     c     50",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>grade</th>\n      <th>rdata</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>b</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>c</td>\n      <td>50</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_df = pd.DataFrame({\"grade\": ['a', 'b', 'c'], \"rdata\": [30, 40, 50]})\n",
    "right_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.965456Z",
     "end_time": "2024-05-09T13:59:08.241722Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "data": {
      "text/plain": "  grade  ldata  rdata\n0     a      0     30\n1     a      3     30\n2     b      1     40\n3     b      2     40\n4     c      4     50\n5     c      5     50",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>grade</th>\n      <th>ldata</th>\n      <th>rdata</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a</td>\n      <td>0</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>a</td>\n      <td>3</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>1</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b</td>\n      <td>2</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>c</td>\n      <td>4</td>\n      <td>50</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>c</td>\n      <td>5</td>\n      <td>50</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left_df, right_df)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.973927Z",
     "end_time": "2024-05-09T13:59:08.241722Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [
    {
     "data": {
      "text/plain": "  grade  ldata  rdata\n0     a      0     30\n1     a      3     30\n2     b      1     40\n3     b      2     40\n4     c      4     50\n5     c      5     50",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>grade</th>\n      <th>ldata</th>\n      <th>rdata</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a</td>\n      <td>0</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>a</td>\n      <td>3</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>1</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b</td>\n      <td>2</td>\n      <td>40</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>c</td>\n      <td>4</td>\n      <td>50</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>c</td>\n      <td>5</td>\n      <td>50</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left_df, right_df, on=\"grade\", how='inner')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.983303Z",
     "end_time": "2024-05-09T13:59:08.264311Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2   A   B\n0   K0   K0  A0  B0\n1   K0   K1  A1  B1\n2   K1   K0  A2  B2\n3   K2   K1  A3  B3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>A</th>\n      <th>B</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldf = pd.DataFrame({\"key1\": ['K0', 'K0', 'K1', 'K2'], 'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                    'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "ldf"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:07.992487Z",
     "end_time": "2024-05-09T13:59:08.269311Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2   C   D\n0   K0   K0  C0  D0\n1   K1   K0  C1  D1\n2   K1   K0  C2  D2\n3   K2   K0  C3  D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K2</td>\n      <td>K0</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdf = pd.DataFrame({\"key1\": ['K0', 'K1', 'K1', 'K2'], 'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                    'C': ['C0', 'C1', 'C2', 'C3'], 'D': ['D0', 'D1', 'D2', 'D3']})\n",
    "rdf"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.001465Z",
     "end_time": "2024-05-09T13:59:08.269311Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2   A   B   C   D\n0   K0   K0  A0  B0  C0  D0\n1   K1   K0  A2  B2  C1  D1\n2   K1   K0  A2  B2  C2  D2",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf, rdf, on=[\"key1\", \"key2\"], how=\"inner\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.011255Z",
     "end_time": "2024-05-09T13:59:08.312636Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [
    {
     "data": {
      "text/plain": "  grade  ldata  rdata\n0     a      0   30.0\n1     b      1   40.0\n2     b      2   40.0\n3     a      3   30.0\n4     c      4   50.0\n5     c      5   50.0\n6     d      6    NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>grade</th>\n      <th>ldata</th>\n      <th>rdata</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>a</td>\n      <td>0</td>\n      <td>30.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>b</td>\n      <td>1</td>\n      <td>40.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>b</td>\n      <td>2</td>\n      <td>40.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>a</td>\n      <td>3</td>\n      <td>30.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>c</td>\n      <td>4</td>\n      <td>50.0</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>c</td>\n      <td>5</td>\n      <td>50.0</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>d</td>\n      <td>6</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left_df, right_df, on=\"grade\", how=\"left\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.022043Z",
     "end_time": "2024-05-09T13:59:08.313639Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2    A    B    C    D\n0   K0   K0   A0   B0   C0   D0\n1   K0   K1   A1   B1  NaN  NaN\n2   K1   K0   A2   B2   C1   D1\n3   K1   K0   A2   B2   C2   D2\n4   K2   K1   A3   B3  NaN  NaN\n5   K2   K0  NaN  NaN   C3   D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>K2</td>\n      <td>K0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf, rdf, on=[\"key1\", \"key2\"], how=\"outer\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.032589Z",
     "end_time": "2024-05-09T13:59:08.334809Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2   A   B    C    D\n0   K0   K0  A0  B0   C0   D0\n1   K0   K1  A1  B1  NaN  NaN\n2   K1   K0  A2  B2   C1   D1\n3   K1   K0  A2  B2   C2   D2\n4   K2   K1  A3  B3  NaN  NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf, rdf, on=[\"key1\", \"key2\"], how=\"left\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.043591Z",
     "end_time": "2024-05-09T13:59:08.356279Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [
    {
     "data": {
      "text/plain": "  key1 key2   A   B    C    D     _merge\n0   K0   K0  A0  B0   C0   D0       both\n1   K0   K1  A1  B1  NaN  NaN  left_only\n2   K1   K0  A2  B2   C1   D1       both\n3   K1   K0  A2  B2   C2   D2       both\n4   K2   K1  A3  B3  NaN  NaN  left_only",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n      <th>_merge</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>left_only</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C1</td>\n      <td>D1</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>left_only</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf, rdf, on=[\"key1\", \"key2\"], how=\"left\", indicator=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.053881Z",
     "end_time": "2024-05-09T13:59:08.356279Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "data": {
      "text/plain": "  jian1 jian2   A   B\n0    K0    K0  A0  B0\n1    K0    K1  A1  B1\n2    K1    K0  A2  B2\n3    K2    K1  A3  B3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian1</th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_ldf = pd.DataFrame({\"jian1\": ['K0', 'K0', 'K1', 'K2'], 'jian2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                        'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3']})\n",
    "new_ldf"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.067258Z",
     "end_time": "2024-05-09T13:59:08.357280Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "  jian1 jian2   A   B key1 key2    C    D     _merge\n0    K0    K0  A0  B0   K0   K0   C0   D0       both\n1    K0    K1  A1  B1  NaN  NaN  NaN  NaN  left_only\n2    K1    K0  A2  B2   K1   K0   C1   D1       both\n3    K1    K0  A2  B2   K1   K0   C2   D2       both\n4    K2    K1  A3  B3  NaN  NaN  NaN  NaN  left_only",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian1</th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n      <th>_merge</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>K0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>left_only</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K1</td>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n      <td>both</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>K2</td>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>left_only</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(new_ldf, rdf, left_on=[\"jian1\", \"jian2\"], right_on=[\"key1\", \"key2\"],\n",
    "         how=\"left\", indicator=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.077384Z",
     "end_time": "2024-05-09T13:59:08.358278Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [
    {
     "data": {
      "text/plain": "     key2   C   D\nkey1             \nK0     K0  C0  D0\nK1     K0  C1  D1\nK1     K0  C2  D2\nK2     K0  C3  D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n    <tr>\n      <th>key1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>K0</th>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>K2</th>\n      <td>K0</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rdf_index = rdf.set_index(\"key1\")\n",
    "rdf_index"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.091855Z",
     "end_time": "2024-05-09T13:59:08.358278Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "data": {
      "text/plain": "      jian2   A   B\njian1              \nK0       K0  A0  B0\nK0       K1  A1  B1\nK1       K0  A2  B2\nK2       K1  A3  B3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n    </tr>\n    <tr>\n      <th>jian1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>K0</th>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n    </tr>\n    <tr>\n      <th>K0</th>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n    </tr>\n    <tr>\n      <th>K2</th>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldf_index = new_ldf.set_index(\"jian1\")\n",
    "ldf_index"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.101240Z",
     "end_time": "2024-05-09T13:59:08.358278Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [
    {
     "data": {
      "text/plain": "      jian2   A   B key2   C   D\njian1                           \nK0       K0  A0  B0   K0  C0  D0\nK0       K1  A1  B1   K0  C0  D0\nK1       K0  A2  B2   K0  C1  D1\nK1       K0  A2  B2   K0  C2  D2\nK2       K1  A3  B3   K0  C3  D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n    <tr>\n      <th>jian1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>K0</th>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>K0</th>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>K2</th>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>K0</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf_index, rdf_index, left_index=True, right_index=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.109739Z",
     "end_time": "2024-05-09T13:59:08.358278Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [
    {
     "data": {
      "text/plain": "      jian2   A   B key2   C   D\njian1                           \nK0       K0  A0  B0   K0  C0  D0\nK0       K1  A1  B1   K0  C0  D0\nK1       K0  A2  B2   K0  C1  D1\nK1       K0  A2  B2   K0  C2  D2\nK2       K1  A3  B3   K0  C3  D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n    <tr>\n      <th>jian1</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>K0</th>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>K0</th>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>K1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>K2</th>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>K0</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ldf_index.join(rdf_index)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.121052Z",
     "end_time": "2024-05-09T13:59:08.359281Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [
    {
     "data": {
      "text/plain": "  jian2   A   B key1 key2   C   D\n0    K0  A0  B0   K0   K0  C0  D0\n0    K1  A1  B1   K0   K0  C0  D0\n1    K0  A2  B2   K1   K0  C1  D1\n2    K0  A2  B2   K1   K0  C2  D2\n3    K1  A3  B3   K2   K0  C3  D3",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>jian2</th>\n      <th>A</th>\n      <th>B</th>\n      <th>key1</th>\n      <th>key2</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>K0</td>\n      <td>A0</td>\n      <td>B0</td>\n      <td>K0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>K1</td>\n      <td>A1</td>\n      <td>B1</td>\n      <td>K0</td>\n      <td>K0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>K0</td>\n      <td>A2</td>\n      <td>B2</td>\n      <td>K1</td>\n      <td>K0</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>K1</td>\n      <td>A3</td>\n      <td>B3</td>\n      <td>K2</td>\n      <td>K0</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(ldf_index, rdf, left_index=True, right_on=\"key1\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.135955Z",
     "end_time": "2024-05-09T13:59:08.359281Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [
    {
     "data": {
      "text/plain": "  state/region     ages  year  population\n0           AL  under18  2012   1117489.0\n1           AL    total  2012   4817528.0\n2           AL  under18  2010   1130966.0\n3           AL    total  2010   4785570.0\n4           AL  under18  2011   1125763.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state/region</th>\n      <th>ages</th>\n      <th>year</th>\n      <th>population</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2012</td>\n      <td>1117489.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>4817528.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2010</td>\n      <td>1130966.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2010</td>\n      <td>4785570.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2011</td>\n      <td>1125763.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = pd.read_csv('state-population.csv')\n",
    "pop.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.147353Z",
     "end_time": "2024-05-09T13:59:08.531661Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [
    {
     "data": {
      "text/plain": "        state  area (sq. mi)\n0     Alabama          52423\n1      Alaska         656425\n2     Arizona         114006\n3    Arkansas          53182\n4  California         163707",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state</th>\n      <th>area (sq. mi)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Alabama</td>\n      <td>52423</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Alaska</td>\n      <td>656425</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Arizona</td>\n      <td>114006</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Arkansas</td>\n      <td>53182</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>California</td>\n      <td>163707</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "areas = pd.read_csv('state-areas.csv')\n",
    "areas.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.165657Z",
     "end_time": "2024-05-09T13:59:08.589320Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "outputs": [
    {
     "data": {
      "text/plain": "        state abbreviation\n0     Alabama           AL\n1      Alaska           AK\n2     Arizona           AZ\n3    Arkansas           AR\n4  California           CA",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state</th>\n      <th>abbreviation</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Alabama</td>\n      <td>AL</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Alaska</td>\n      <td>AK</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Arizona</td>\n      <td>AZ</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Arkansas</td>\n      <td>AR</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>California</td>\n      <td>CA</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abbrevs = pd.read_csv('state-abbrevs.csv')\n",
    "abbrevs.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.174841Z",
     "end_time": "2024-05-09T13:59:08.649520Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "outputs": [
    {
     "data": {
      "text/plain": "  state/region     ages  year  population    state\n0           AL  under18  2012   1117489.0  Alabama\n1           AL    total  2012   4817528.0  Alabama\n2           AL  under18  2010   1130966.0  Alabama\n3           AL    total  2010   4785570.0  Alabama\n4           AL  under18  2011   1125763.0  Alabama",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state/region</th>\n      <th>ages</th>\n      <th>year</th>\n      <th>population</th>\n      <th>state</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2012</td>\n      <td>1117489.0</td>\n      <td>Alabama</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>4817528.0</td>\n      <td>Alabama</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2010</td>\n      <td>1130966.0</td>\n      <td>Alabama</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2010</td>\n      <td>4785570.0</td>\n      <td>Alabama</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2011</td>\n      <td>1125763.0</td>\n      <td>Alabama</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_merged = pd.merge(pop, abbrevs, left_on=\"state/region\",\n",
    "                      right_on=\"abbreviation\", how=\"outer\")\n",
    "pop_merged = pop_merged.drop('abbreviation', axis=1)\n",
    "pop_merged.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.186124Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "outputs": [
    {
     "data": {
      "text/plain": "state/region    False\nages            False\nyear            False\npopulation       True\nstate            True\ndtype: bool"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_merged.isnull().any()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.197135Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "outputs": [
    {
     "data": {
      "text/plain": "     state/region     ages  year  population state\n2448           PR  under18  1990         NaN   NaN\n2449           PR    total  1990         NaN   NaN\n2450           PR    total  1991         NaN   NaN\n2451           PR  under18  1991         NaN   NaN\n2452           PR    total  1993         NaN   NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state/region</th>\n      <th>ages</th>\n      <th>year</th>\n      <th>population</th>\n      <th>state</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2448</th>\n      <td>PR</td>\n      <td>under18</td>\n      <td>1990</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2449</th>\n      <td>PR</td>\n      <td>total</td>\n      <td>1990</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2450</th>\n      <td>PR</td>\n      <td>total</td>\n      <td>1991</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2451</th>\n      <td>PR</td>\n      <td>under18</td>\n      <td>1991</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>2452</th>\n      <td>PR</td>\n      <td>total</td>\n      <td>1993</td>\n      <td>NaN</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_merged[pop_merged['population'].isnull()].head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.208082Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "outputs": [
    {
     "data": {
      "text/plain": "array(['PR', 'USA'], dtype=object)"
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_merged.loc[pop_merged['state'].isnull(), 'state/region'].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.218427Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "outputs": [
    {
     "data": {
      "text/plain": "state/region    False\nages            False\nyear            False\npopulation       True\nstate           False\ndtype: bool"
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_merged.loc[pop_merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'\n",
    "pop_merged.loc[pop_merged['state/region'] == 'USA', 'state'] = 'United States'\n",
    "pop_merged.isnull().any()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.229602Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "outputs": [
    {
     "data": {
      "text/plain": "  state/region     ages  year  population    state  area (sq. mi)\n0           AL  under18  2012   1117489.0  Alabama        52423.0\n1           AL    total  2012   4817528.0  Alabama        52423.0\n2           AL  under18  2010   1130966.0  Alabama        52423.0\n3           AL    total  2010   4785570.0  Alabama        52423.0\n4           AL  under18  2011   1125763.0  Alabama        52423.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state/region</th>\n      <th>ages</th>\n      <th>year</th>\n      <th>population</th>\n      <th>state</th>\n      <th>area (sq. mi)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2012</td>\n      <td>1117489.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>4817528.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2010</td>\n      <td>1130966.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2010</td>\n      <td>4785570.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>AL</td>\n      <td>under18</td>\n      <td>2011</td>\n      <td>1125763.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = pd.merge(pop_merged, areas, on='state', how='left')\n",
    "result.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.237720Z",
     "end_time": "2024-05-09T13:59:08.731907Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "outputs": [
    {
     "data": {
      "text/plain": "state/region     False\nages             False\nyear             False\npopulation        True\nstate            False\narea (sq. mi)     True\ndtype: bool"
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.isnull().any()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.250242Z",
     "end_time": "2024-05-09T13:59:08.851085Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "outputs": [
    {
     "data": {
      "text/plain": "array(['United States'], dtype=object)"
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.loc[result['area (sq. mi)'].isnull(), 'state'].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.259912Z",
     "end_time": "2024-05-09T13:59:08.851085Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "outputs": [
    {
     "data": {
      "text/plain": "array(['United States'], dtype=object)"
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result['state'][result['area (sq. mi)'].isnull()].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.267313Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "outputs": [
    {
     "data": {
      "text/plain": "state/region     False\nages             False\nyear             False\npopulation       False\nstate            False\narea (sq. mi)    False\ndtype: bool"
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.dropna(inplace=True)\n",
    "result.isnull().any()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.277871Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "outputs": [
    {
     "data": {
      "text/plain": "    state/region   ages  year  population       state  area (sq. mi)\n1             AL  total  2012   4817528.0     Alabama        52423.0\n95            AK  total  2012    730307.0      Alaska       656425.0\n97            AZ  total  2012   6551149.0     Arizona       114006.0\n191           AR  total  2012   2949828.0    Arkansas        53182.0\n193           CA  total  2012  37999878.0  California       163707.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>state/region</th>\n      <th>ages</th>\n      <th>year</th>\n      <th>population</th>\n      <th>state</th>\n      <th>area (sq. mi)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>AL</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>4817528.0</td>\n      <td>Alabama</td>\n      <td>52423.0</td>\n    </tr>\n    <tr>\n      <th>95</th>\n      <td>AK</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>730307.0</td>\n      <td>Alaska</td>\n      <td>656425.0</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>AZ</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>6551149.0</td>\n      <td>Arizona</td>\n      <td>114006.0</td>\n    </tr>\n    <tr>\n      <th>191</th>\n      <td>AR</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>2949828.0</td>\n      <td>Arkansas</td>\n      <td>53182.0</td>\n    </tr>\n    <tr>\n      <th>193</th>\n      <td>CA</td>\n      <td>total</td>\n      <td>2012</td>\n      <td>37999878.0</td>\n      <td>California</td>\n      <td>163707.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2012 = result.query(\"year==2012 & ages=='total'\")\n",
    "d2012.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.287905Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "outputs": [
    {
     "data": {
      "text/plain": "state\nAlabama        91.897221\nAlaska          1.112552\nArizona        57.463195\nArkansas       55.466662\nCalifornia    232.121278\ndtype: float64"
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2012 = result.query(\"year==2012 & ages=='total'\")\n",
    "d2012.set_index('state', inplace=True)\n",
    "density = d2012['population'] / d2012['area (sq. mi)']\n",
    "density.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.303915Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "outputs": [
    {
     "data": {
      "text/plain": "state\nDistrict of Columbia    9315.102941\nPuerto Rico             1038.846373\nNew Jersey              1016.710502\nRhode Island             679.808414\nConnecticut              647.865260\ndtype: float64"
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.sort_values(ascending=False, inplace=True)\n",
    "density.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.312636Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "outputs": [
    {
     "data": {
      "text/plain": "state\nSouth Dakota    10.814785\nNorth Dakota     9.919453\nMontana          6.837955\nWyoming          5.894886\nAlaska           1.112552\ndtype: float64"
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.tail()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.324648Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 3.组合数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "outputs": [
    {
     "data": {
      "text/plain": "array([ 1.,  2.,  4., nan,  5., 10.])"
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = pd.Series([np.nan, 2, 4, np.nan, 8, 10])\n",
    "b = pd.Series([1, 2, np.nan, np.nan, 5, np.nan])\n",
    "np.where(pd.isnull(b), a, b)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.332811Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "outputs": [
    {
     "data": {
      "text/plain": "0     1.0\n1     2.0\n2     4.0\n3     NaN\n4     5.0\n5    10.0\ndtype: float64"
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b.combine_first(a)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.341622Z",
     "end_time": "2024-05-09T13:59:08.886146Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "outputs": [
    {
     "data": {
      "text/plain": "    one   two\n0  11.0   NaN\n1  12.0  22.0\n2   NaN  23.0\n3  13.0   NaN\n4   NaN  24.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>11.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>12.0</td>\n      <td>22.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>NaN</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>13.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>NaN</td>\n      <td>24.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({\"one\": [11, 12, np.nan, 13, np.nan], \"two\": [np.nan, 22, 23, np.nan, 24]})\n",
    "df1"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.356279Z",
     "end_time": "2024-05-09T13:59:08.966932Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "outputs": [
    {
     "data": {
      "text/plain": "   one    two        three\n0    0   10.0   100.000000\n1    1   28.0   158.489319\n2    2   46.0   251.188643\n3    3   64.0   398.107171\n4    4   82.0   630.957344\n5    5  100.0  1000.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n      <th>three</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>10.0</td>\n      <td>100.000000</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>28.0</td>\n      <td>158.489319</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>46.0</td>\n      <td>251.188643</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>64.0</td>\n      <td>398.107171</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>82.0</td>\n      <td>630.957344</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>5</td>\n      <td>100.0</td>\n      <td>1000.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({\"one\": np.arange(6), \"two\": np.linspace(10, 100, 6),\n",
    "                    \"three\": np.logspace(2, 3, 6)})\n",
    "df2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.366384Z",
     "end_time": "2024-05-09T13:59:08.967765Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "outputs": [
    {
     "data": {
      "text/plain": "    one        three    two\n0  11.0   100.000000   10.0\n1  12.0   158.489319   22.0\n2   2.0   251.188643   23.0\n3  13.0   398.107171   64.0\n4   4.0   630.957344   24.0\n5   5.0  1000.000000  100.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>three</th>\n      <th>two</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>11.0</td>\n      <td>100.000000</td>\n      <td>10.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>12.0</td>\n      <td>158.489319</td>\n      <td>22.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2.0</td>\n      <td>251.188643</td>\n      <td>23.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>13.0</td>\n      <td>398.107171</td>\n      <td>64.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4.0</td>\n      <td>630.957344</td>\n      <td>24.0</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>5.0</td>\n      <td>1000.000000</td>\n      <td>100.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.combine_first(df2)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.379306Z",
     "end_time": "2024-05-09T13:59:08.967765Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### 4.数据转换"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "outputs": [
    {
     "data": {
      "text/plain": "     a  b  c  d  e\none  0  1  2  3  4\ntwo  5  6  7  8  9",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>one</th>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.arange(10).reshape(2, 5),\n",
    "                    index=['one', 'two'], columns=['a', 'b', 'c', 'd', 'e'])\n",
    "data"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.393518Z",
     "end_time": "2024-05-09T13:59:08.984222Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "outputs": [
    {
     "data": {
      "text/plain": "one  a    0\n     b    1\n     c    2\n     d    3\n     e    4\ntwo  a    5\n     b    6\n     c    7\n     d    8\n     e    9\ndtype: int32"
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.402696Z",
     "end_time": "2024-05-09T13:59:08.990225Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "outputs": [
    {
     "data": {
      "text/plain": "     a  b  c  d  e\none  0  1  2  3  4\ntwo  5  6  7  8  9",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>one</th>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack().unstack()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.413669Z",
     "end_time": "2024-05-09T13:59:08.991225Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "outputs": [
    {
     "data": {
      "text/plain": "     a  b  c  d   e\none  0  1  2  3 NaN\ntwo  5  6  7  8 NaN",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>a</th>\n      <th>b</th>\n      <th>c</th>\n      <th>d</th>\n      <th>e</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>one</th>\n      <td>0</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>two</th>\n      <td>5</td>\n      <td>6</td>\n      <td>7</td>\n      <td>8</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['e'] = np.nan\n",
    "data"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.426943Z",
     "end_time": "2024-05-09T13:59:08.999341Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "outputs": [
    {
     "data": {
      "text/plain": "one  a    0.0\n     b    1.0\n     c    2.0\n     d    3.0\ntwo  a    5.0\n     b    6.0\n     c    7.0\n     d    8.0\ndtype: float64"
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.434408Z",
     "end_time": "2024-05-09T13:59:09.034779Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "outputs": [
    {
     "data": {
      "text/plain": "one  a    0.0\n     b    1.0\n     c    2.0\n     d    3.0\n     e    NaN\ntwo  a    5.0\n     b    6.0\n     c    7.0\n     d    8.0\n     e    NaN\ndtype: float64"
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.stack(dropna=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.450720Z",
     "end_time": "2024-05-09T13:59:09.034779Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "outputs": [
    {
     "data": {
      "text/plain": "    class  subject  numbers\n0  class1  physics       28\n1  class2   python       30\n2  class1     math       20\n3  class2  physics       80",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>class</th>\n      <th>subject</th>\n      <th>numbers</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>class1</td>\n      <td>physics</td>\n      <td>28</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>class2</td>\n      <td>python</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>class1</td>\n      <td>math</td>\n      <td>20</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>class2</td>\n      <td>physics</td>\n      <td>80</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students = pd.DataFrame({\"class\": ['class1', 'class2', 'class1', 'class2'],\n",
    "                         \"subject\": [\"physics\", \"python\", \"math\", \"physics\"],\n",
    "                         \"numbers\": [28, 30, 20, 80]})\n",
    "\n",
    "students"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.463982Z",
     "end_time": "2024-05-09T13:59:09.122722Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "outputs": [
    {
     "data": {
      "text/plain": "subject  math  physics  python\nclass                         \nclass1   20.0     28.0     NaN\nclass2    NaN     80.0    30.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>subject</th>\n      <th>math</th>\n      <th>physics</th>\n      <th>python</th>\n    </tr>\n    <tr>\n      <th>class</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>class1</th>\n      <td>20.0</td>\n      <td>28.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>class2</th>\n      <td>NaN</td>\n      <td>80.0</td>\n      <td>30.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students.pivot(index='class', columns='subject', values='numbers')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.518061Z",
     "end_time": "2024-05-09T13:59:09.123729Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "outputs": [
    {
     "data": {
      "text/plain": "    class  subject  numbers\n0  class1  physics       28\n1  class2   python       30\n2  class1     math       20\n3  class2  physics       80\n4  class2   python       99",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>class</th>\n      <th>subject</th>\n      <th>numbers</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>class1</td>\n      <td>physics</td>\n      <td>28</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>class2</td>\n      <td>python</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>class1</td>\n      <td>math</td>\n      <td>20</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>class2</td>\n      <td>physics</td>\n      <td>80</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>class2</td>\n      <td>python</td>\n      <td>99</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students2 = pd.DataFrame({\"class\": ['class1', 'class2', 'class1', 'class2', 'class2'],\n",
    "                          \"subject\": [\"physics\", \"python\", \"math\", \"physics\", \"python\"],\n",
    "                          \"numbers\": [28, 30, 20, 80, 99]})\n",
    "students2"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.530662Z",
     "end_time": "2024-05-09T13:59:09.146964Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\HP\\AppData\\Local\\Temp\\ipykernel_15764\\2207975804.py:1: FutureWarning: The provided callable <function sum at 0x000002471CD9EB60> is currently using DataFrameGroupBy.sum. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string \"sum\" instead.\n",
      "  students2.pivot_table(index='class', columns='subject', values='numbers', aggfunc=np.sum)\n"
     ]
    },
    {
     "data": {
      "text/plain": "subject  math  physics  python\nclass                         \nclass1   20.0     28.0     NaN\nclass2    NaN     80.0   129.0",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>subject</th>\n      <th>math</th>\n      <th>physics</th>\n      <th>python</th>\n    </tr>\n    <tr>\n      <th>class</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>class1</th>\n      <td>20.0</td>\n      <td>28.0</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>class2</th>\n      <td>NaN</td>\n      <td>80.0</td>\n      <td>129.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students2.pivot_table(index='class', columns='subject', values='numbers', aggfunc=np.sum)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.541439Z",
     "end_time": "2024-05-09T13:59:09.148323Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "outputs": [
    {
     "data": {
      "text/plain": "   year  birth_rate  death_rate  growth_rate\n0  1978       18.25        6.25        12.00\n1  1980       18.21        6.34        11.87\n2  1981       20.91        6.36        14.55\n3  1982       22.28        6.60        15.68\n4  1983       20.19        6.90        13.29",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>birth_rate</th>\n      <th>death_rate</th>\n      <th>growth_rate</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1978</td>\n      <td>18.25</td>\n      <td>6.25</td>\n      <td>12.00</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1980</td>\n      <td>18.21</td>\n      <td>6.34</td>\n      <td>11.87</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1981</td>\n      <td>20.91</td>\n      <td>6.36</td>\n      <td>14.55</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1982</td>\n      <td>22.28</td>\n      <td>6.60</td>\n      <td>15.68</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1983</td>\n      <td>20.19</td>\n      <td>6.90</td>\n      <td>13.29</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cnpop = pd.read_csv('cnpop.csv', encoding='utf-8')\n",
    "cnpop.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.556527Z",
     "end_time": "2024-05-09T13:59:09.200500Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "outputs": [
    {
     "data": {
      "text/plain": "   year  birth_rate  death_rate  growth_rate  decade\n0  1978       18.25        6.25        12.00    1970\n1  1980       18.21        6.34        11.87    1980\n2  1981       20.91        6.36        14.55    1980\n3  1982       22.28        6.60        15.68    1980\n4  1983       20.19        6.90        13.29    1980",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>year</th>\n      <th>birth_rate</th>\n      <th>death_rate</th>\n      <th>growth_rate</th>\n      <th>decade</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1978</td>\n      <td>18.25</td>\n      <td>6.25</td>\n      <td>12.00</td>\n      <td>1970</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1980</td>\n      <td>18.21</td>\n      <td>6.34</td>\n      <td>11.87</td>\n      <td>1980</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1981</td>\n      <td>20.91</td>\n      <td>6.36</td>\n      <td>14.55</td>\n      <td>1980</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1982</td>\n      <td>22.28</td>\n      <td>6.60</td>\n      <td>15.68</td>\n      <td>1980</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1983</td>\n      <td>20.19</td>\n      <td>6.90</td>\n      <td>13.29</td>\n      <td>1980</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cnpop['decade'] = cnpop['year'] // 10 * 10\n",
    "cnpop.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.566902Z",
     "end_time": "2024-05-09T13:59:09.226785Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "outputs": [
    {
     "data": {
      "text/plain": "        birth_rate  death_rate  growth_rate\ndecade                                     \n1970     18.250000    6.250000    12.000000\n1980     21.224000    6.656000    14.568000\n1990     17.572000    6.574000    10.998000\n2000     12.565000    6.650000     5.915000\n2010     11.976667    7.133333     4.843333",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>birth_rate</th>\n      <th>death_rate</th>\n      <th>growth_rate</th>\n    </tr>\n    <tr>\n      <th>decade</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1970</th>\n      <td>18.250000</td>\n      <td>6.250000</td>\n      <td>12.000000</td>\n    </tr>\n    <tr>\n      <th>1980</th>\n      <td>21.224000</td>\n      <td>6.656000</td>\n      <td>14.568000</td>\n    </tr>\n    <tr>\n      <th>1990</th>\n      <td>17.572000</td>\n      <td>6.574000</td>\n      <td>10.998000</td>\n    </tr>\n    <tr>\n      <th>2000</th>\n      <td>12.565000</td>\n      <td>6.650000</td>\n      <td>5.915000</td>\n    </tr>\n    <tr>\n      <th>2010</th>\n      <td>11.976667</td>\n      <td>7.133333</td>\n      <td>4.843333</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cnpop.pivot_table(index='decade', aggfunc='mean',\n",
    "                  values=['birth_rate', 'death_rate', 'growth_rate'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.586322Z",
     "end_time": "2024-05-09T13:59:09.245791Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-05-09T13:59:08.629763Z",
     "end_time": "2024-05-09T13:59:09.245791Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
